package ru.spies.requests;

import java.io.UnsupportedEncodingException;
import java.sql.Date;
import java.text.DateFormat;
import java.text.ParseException;
import java.util.Collection;

import javax.servlet.http.HttpServletRequest;

import ru.spies.dbwork.DBWork;
import ru.spies.entities.Agent;
import ru.spies.entities.Agentsandtask;
import ru.spies.entities.Country;
import ru.spies.entities.Department;
import ru.spies.entities.Document;
import ru.spies.entities.ITableClass;
import ru.spies.entities.Importance;
import ru.spies.entities.Task;
import ru.spies.entities.TaskType;
import ru.spies.help.ISetOperations;
import ru.spies.help.MyLogger;
import ru.spies.help.OperationsData;

public class UpdateRequestWork extends AbstractRequestWork {

	private static UpdateRequestWork statThis=null;

	private static final String WHERE=" WHERE ";
	
	private UpdateRequestWork() {
		
	}

	public static UpdateRequestWork getInstance(){
		if (statThis==null){
			statThis= new UpdateRequestWork();
		}	
		return statThis;
	}
	
	public Collection<ITableClass> getCountries(HttpServletRequest request){
		try {
			request.setCharacterEncoding("UTF-8");
		} catch (UnsupportedEncodingException e) {
			MyLogger.getInstance().getLog().error(MyLogger.CHARSET_EXCEPTION, e);
		}
		String withCountry=request.getParameter("withCountry");
		StringBuffer queryStr=new StringBuffer("SELECT o FROM Country o");
		StringBuffer secondQueryPart=new StringBuffer();
		boolean needAnd=false;
		boolean wasWhere=false;
		OperationsData opData=new OperationsData();
		if (withCountry!=null){
			secondQueryPart.append(WHERE);
			wasWhere=true;
			secondQueryPart.append("o.id="+request.getParameter("countryName"));
			needAnd=true;
			ISetOperations operation = new ISetOperations() {
				
				@Override
				public void doSet(ITableClass tableObject, String param) {
					Country country = (Country) tableObject;
					country.setName(param);
				}
			};
			opData.addNewOperation(operation, request.getParameter("newCountryName"));
		}
		String withRelation=request.getParameter("withRelation");
		if (withRelation!=null){
			if (needAnd){
				secondQueryPart.append(" and ");
			}
			if (!wasWhere){
				secondQueryPart.append(WHERE);
				wasWhere=true;
			}
			secondQueryPart.append("o.relation='"+request.getParameter("relation")+"'");
			ISetOperations operation = new ISetOperations() {
				
				@Override
				public void doSet(ITableClass tableObject, String param) {
					Country country = (Country) tableObject;
					country.setRelation(param);;
				}
			};
			opData.addNewOperation(operation, request.getParameter("newRelation"));
		}
		if (!wasWhere){
			return null;
		}
		queryStr.append(secondQueryPart);
		Collection<ITableClass> countries = (Collection<ITableClass>) DBWork.getInstance().getTableObjects(queryStr.toString());
		for (ITableClass i : countries) {
			opData.doOperations(i);
		}
		return countries;
	}
	
	public Collection<ITableClass> getAgents(HttpServletRequest request){
		try {
			request.setCharacterEncoding("UTF-8");
		} catch (UnsupportedEncodingException e) {
			MyLogger.getInstance().getLog().error(MyLogger.CHARSET_EXCEPTION, e);
		}
		String withName=request.getParameter("withName");
		StringBuffer queryStr=new StringBuffer("SELECT o FROM Agent o");
		StringBuffer secondQueryPart=new StringBuffer();
		boolean needAnd=false;
		boolean wasWhere=false;
		OperationsData opData=new OperationsData();
		if (withName!=null){
			secondQueryPart.append(WHERE);
			wasWhere=true;
			secondQueryPart.append("o.id="+request.getParameter("agentName"));
			needAnd=true;
			ISetOperations operation = new ISetOperations() {
				
				@Override
				public void doSet(ITableClass tableObject, String param) {
					Agent agent = (Agent) tableObject;
					agent.setName(param);
				}
			};
			opData.addNewOperation(operation, request.getParameter("newAgentName"));
		}
		String withRecruiter=request.getParameter("withRecruiter");
		if (withRecruiter!=null){
			if (needAnd){
				secondQueryPart.append(" and ");
			}
			if (!wasWhere){
				secondQueryPart.append(WHERE);
				wasWhere=true;
			}
			queryStr.append(", Agent r");
			secondQueryPart.append("o.recruiter=r and r.id="+request.getParameter("idRecruiter"));
			needAnd=true;
			ISetOperations operation = new ISetOperations() {
				
				@Override
				public void doSet(ITableClass tableObject, String param) {
					Agent agent = (Agent) tableObject;
					Agent recruiter;
					if (changeIfNull(param)!=null){
						recruiter= new Agent();
						recruiter.setParamForFindQuery(Integer.valueOf(param));
						recruiter = (Agent) DBWork.getInstance().findObjectFromTable(recruiter);
					} else {
						recruiter=null;
					}
					agent.setRecruiter(recruiter );
				}
			};
			opData.addNewOperation(operation, request.getParameter("newIdRecruiter"));
		}
		String withDepartment=request.getParameter("withDepartment");
		if (withDepartment!=null){
			if (needAnd){
				secondQueryPart.append(" and ");
			}
			if (!wasWhere){
				secondQueryPart.append(WHERE);
				wasWhere=true;
			}
			queryStr.append(",Department d");
			secondQueryPart.append("o.department=d and d.id="+request.getParameter("idDepartment"));
			needAnd=true;
			ISetOperations operation = new ISetOperations() {
				
				@Override
				public void doSet(ITableClass tableObject, String param) {
					Agent agent = (Agent) tableObject;
					if (changeIfNull(param)!=null){
						Department dep = new Department();
						dep.setParamForFindQuery(Integer.valueOf(param));
						dep = (Department) DBWork.getInstance().findObjectFromTable(dep);
						if (agent.getDepartment()!=null){
							agent.getDepartment().removeAgent(agent);
						}

						dep.addAgent(agent);
					} else {
						agent.setDepartment(null);
					}
				}
			};
			opData.addNewOperation(operation, request.getParameter("newIdDepartment"));
		}
		String withStatus=request.getParameter("withStatus");
		if (withStatus!=null){
			if (needAnd){
				secondQueryPart.append(" and ");
			}
			if (!wasWhere){
				secondQueryPart.append(WHERE);
				wasWhere=true;
			}
			secondQueryPart.append("o.status="+Boolean.valueOf(request.getParameter("status")));
			ISetOperations operation = new ISetOperations() {
				
				@Override
				public void doSet(ITableClass tableObject, String param) {
					Agent agent = (Agent) tableObject;
					agent.setStatus(Boolean.valueOf(param));
				}
			};
			opData.addNewOperation(operation, request.getParameter("newStatus"));
		}
		if (!wasWhere){
			return null;
		}
		queryStr.append(secondQueryPart);
		Collection<ITableClass> agents = (Collection<ITableClass>) DBWork.getInstance().getTableObjects(queryStr.toString());
		for (ITableClass i : agents) {
			opData.doOperations(i);
		}
		return agents;
	}
	
	public Collection<ITableClass> getDepartments(HttpServletRequest request){
		try {
			request.setCharacterEncoding("UTF-8");
		} catch (UnsupportedEncodingException e) {
			MyLogger.getInstance().getLog().error(MyLogger.CHARSET_EXCEPTION, e);
		}
		String withName=request.getParameter("withName");
		StringBuffer queryStr=new StringBuffer("SELECT o FROM Department o");
		StringBuffer secondQueryPart=new StringBuffer();
		boolean needAnd=false;
		boolean wasWhere=false;
		OperationsData opData=new OperationsData();
		if (withName!=null){
			secondQueryPart.append(WHERE);
			wasWhere=true;
			secondQueryPart.append("o.id="+request.getParameter("depName"));
			needAnd=true;
			ISetOperations operation = new ISetOperations() {
				
				@Override
				public void doSet(ITableClass tableObject, String param) {
					Department dep = (Department) tableObject;
					dep.setName(param);
				}
			};
			opData.addNewOperation(operation, request.getParameter("newDepName"));
		}
		String withCreationDate=request.getParameter("withCreationDate");
		if (withCreationDate!=null){
			if (needAnd){
				secondQueryPart.append(" and ");
			}
			if (!wasWhere){
				secondQueryPart.append(WHERE);
				wasWhere=true;
			}
			Date searchDate = null;
			try {
				searchDate = new Date(DateFormat.getInstance().parse(request.getParameter("creationDate")).getTime());
			} catch (ParseException e) {
				MyLogger.getInstance().getLog().error(MyLogger.EXCEPTION,e);
				return null;
			}
			secondQueryPart.append("o.creationDate="+searchDate);
			needAnd=true;
		}
		String withCountry=request.getParameter("withCountry");
		if (withCountry!=null){
			if (needAnd){
				secondQueryPart.append(" and ");
			}
			if (!wasWhere){
				queryStr.append(", Country c");
				secondQueryPart.append(WHERE);
				wasWhere=true;
			}
			secondQueryPart.append("o.country=c and c.id="+request.getParameter("idCountry"));
			needAnd=true;
			ISetOperations operation = new ISetOperations() {
				
				@Override
				public void doSet(ITableClass tableObject, String param) {
					Department dep = (Department) tableObject;
					if (changeIfNull(param)!=null){
						Country country= new Country();
						country.setParamForFindQuery(Integer.valueOf(param));
						country = (Country) DBWork.getInstance().findObjectFromTable(country);
						if (dep.getCountry()!=null){
							dep.getCountry().removeDepartment(dep);
						}
						country.addDepartment(dep);
					} else {
						dep.setCountry(null);
					}
				}
			};
			opData.addNewOperation(operation, request.getParameter("newIdCountry"));
		}
		String withResident=request.getParameter("withResident");
		if (withResident!=null){
			if (needAnd){
				secondQueryPart.append(" and ");
			}
			if (!wasWhere){
				secondQueryPart.append(WHERE);
				wasWhere=true;
			}
			queryStr.append(", Agent r ");
			secondQueryPart.append("o.resident=r and r.id="+request.getParameter("idResident"));
			ISetOperations operation = new ISetOperations() {
				
				@Override
				public void doSet(ITableClass tableObject, String param) {
					Department dep = (Department) tableObject;
					Agent agent;
					if (changeIfNull(param)!=null){
						agent= new Agent();
						agent.setParamForFindQuery(Integer.valueOf(param));
						agent = (Agent) DBWork.getInstance().findObjectFromTable(agent);
					} else {
						agent=null;
					}
					dep.setResident(agent);
				}
			};
			opData.addNewOperation(operation, request.getParameter("newIdResident"));
		}
		if (!wasWhere){
			return null;
		}
		queryStr.append(secondQueryPart);
		Collection<ITableClass> departments = (Collection<ITableClass>) DBWork.getInstance().getTableObjects(queryStr.toString());
		for (ITableClass i : departments) {
			opData.doOperations(i);
		}
		return departments;
	}
	
	public Collection<ITableClass> getTasks(HttpServletRequest request){
		try {
			request.setCharacterEncoding("UTF-8");
		} catch (UnsupportedEncodingException e) {
			MyLogger.getInstance().getLog().error(MyLogger.CHARSET_EXCEPTION, e);
		}
		String withName=request.getParameter("withName");
		StringBuffer queryStr=new StringBuffer();
		StringBuffer secondQueryPart=new StringBuffer("SELECT o FROM Task o");
		boolean needAnd=false;
		boolean wasWhere=false;
		OperationsData opData=new OperationsData();
		if (withName!=null){
			secondQueryPart.append(WHERE);
			wasWhere=true;
			secondQueryPart.append("o.id="+request.getParameter("taskName"));
			needAnd=true;
			ISetOperations operation = new ISetOperations() {
				
				@Override
				public void doSet(ITableClass tableObject, String param) {
					Task task = (Task) tableObject;
					task.setName(param);
				}
			};
			opData.addNewOperation(operation, request.getParameter("newTaskName"));
		}
		String withStatus=request.getParameter("withStatus");
		if (withStatus!=null){
			if (needAnd){
				secondQueryPart.append(" and ");
			}
			if (!wasWhere){
				secondQueryPart.append(WHERE);
				wasWhere=true;
			}
			secondQueryPart.append("o.status='"+request.getParameter("status")+"'");
			needAnd=true;
			ISetOperations operation = new ISetOperations() {
				
				@Override
				public void doSet(ITableClass tableObject, String param) {
					Task task = (Task) tableObject;
					task.setStatus(param);
				}
			};
			opData.addNewOperation(operation, request.getParameter("newStatus"));
		}
		String withCountry=request.getParameter("withCountry");
		if (withCountry!=null){
			if (needAnd){
				secondQueryPart.append(" and ");
			}
			if (!wasWhere){
				secondQueryPart.append(WHERE);
				wasWhere=true;
			}
			queryStr.append(", Country c ");
			secondQueryPart.append("o.country=c and c.id="+request.getParameter("idCountry"));
			needAnd=true;
			ISetOperations operation = new ISetOperations() {
				
				@Override
				public void doSet(ITableClass tableObject, String param) {
					Task task = (Task) tableObject;
					if (changeIfNull(param)!=null){
						Country country= new Country();
						country.setParamForFindQuery(Integer.valueOf(param));
						country = (Country) DBWork.getInstance().findObjectFromTable(country);
						if (task.getCountry()!=null){
							task.getCountry().removeTask(task);
						}
						country.addTask(task);
					} else {
						task.setCountry(null);
					}
				}
			};
			opData.addNewOperation(operation, request.getParameter("newIdCountry"));
		}
		String withImportance=request.getParameter("withImportance");
		if (withImportance!=null){
			if (needAnd){
				secondQueryPart.append(" and ");
			}
			if (!wasWhere){
				secondQueryPart.append(WHERE);
				wasWhere=true;
			}
			queryStr.append(", Importance i");
			secondQueryPart.append("o.importance=i and i.id="+request.getParameter("idImportance"));
			needAnd=true;
			ISetOperations operation = new ISetOperations() {
				
				@Override
				public void doSet(ITableClass tableObject, String param) {
					Task task = (Task) tableObject;
					if (changeIfNull(param)!=null){
						Importance importance= new Importance();
						importance.setParamForFindQuery(Integer.valueOf(param));
						importance = (Importance) DBWork.getInstance().findObjectFromTable(importance);
						if (task.getImportance()!=null){
							task.getImportance().removeTask(task);
						}
						importance.addTask(task);
					} else {
						task.setImportance(null);
					}
				}
			};
			opData.addNewOperation(operation, request.getParameter("newIdImportance"));
		}
		String withTaskType=request.getParameter("withTaskType");
		if (withTaskType!=null){
			if (needAnd){
				secondQueryPart.append(" and ");
			}
			if (!wasWhere){
				secondQueryPart.append(WHERE);
				wasWhere=true;
			}
			queryStr.append(", TaskType tt");
			secondQueryPart.append("o.taskType=tt and tt.id="+request.getParameter("idTaskType"));
			ISetOperations operation = new ISetOperations() {
				
				@Override
				public void doSet(ITableClass tableObject, String param) {
					Task task = (Task) tableObject;
					if (changeIfNull(param)!=null){
						TaskType taskType= new TaskType();
						taskType.setParamForFindQuery(Integer.valueOf(param));
						taskType = (TaskType) DBWork.getInstance().findObjectFromTable(taskType);
						if (task.getTaskType()!=null){
						task.getTaskType().removeTask(task);
						}
						taskType.addTask(task);
					} else {
						task.setCountry(null);
					}
				}
			};
			opData.addNewOperation(operation, request.getParameter("newIdTaskType"));
		}
		if (!wasWhere){
			return null;
		}
		queryStr.append(secondQueryPart);
		Collection<ITableClass> tasks = (Collection<ITableClass>) DBWork.getInstance().getTableObjects(queryStr.toString());
		for (ITableClass i : tasks) {
			opData.doOperations(i);
		}
		return tasks;
	}
	
	public Collection<ITableClass> getDocuments(HttpServletRequest request){
		try {
			request.setCharacterEncoding("UTF-8");
		} catch (UnsupportedEncodingException e) {
			MyLogger.getInstance().getLog().error(MyLogger.CHARSET_EXCEPTION, e);
		}
		StringBuffer queryStr=new StringBuffer("SELECT o FROM Document o");
		StringBuffer secondQueryPart=new StringBuffer();
		String withTextLink=request.getParameter("withTextLink");
		boolean needAnd=false;
		boolean wasWhere=false;
		OperationsData opData=new OperationsData();
		if (withTextLink!=null){
			secondQueryPart.append(WHERE);
			wasWhere=true;
			secondQueryPart.append("o.id="+request.getParameter("textLink"));
			needAnd=true;
			ISetOperations operation = new ISetOperations() {
				
				@Override
				public void doSet(ITableClass tableObject, String param) {
					Document doc = (Document) tableObject;
					doc.setTextLink(param);
				}
			};
			opData.addNewOperation(operation, request.getParameter("newTextLink"));
		}
		String withImportance=request.getParameter("withImportance");
		if (withImportance!=null){
			if (needAnd){
				secondQueryPart.append(" and ");
			}
			if (!wasWhere){
				queryStr.append(", Importance i ");
				secondQueryPart.append(WHERE);
				wasWhere=true;
			}
			secondQueryPart.append("o.importance=i and i.id="+request.getParameter("idImportance"));
			needAnd=true;
			ISetOperations operation = new ISetOperations() {
				
				@Override
				public void doSet(ITableClass tableObject, String param) {
					Document doc = (Document) tableObject;
					if (changeIfNull(param)!=null){
						Importance importance= new Importance();
						importance.setParamForFindQuery(Integer.valueOf(param));
						importance = (Importance) DBWork.getInstance().findObjectFromTable(importance);
						if (doc.getImportance()!=null){
							doc.getImportance().removeDocument(doc);
						}
						importance.addDocument(doc);
					} else {
						doc.setImportance(null);
					}
				}
			};
			opData.addNewOperation(operation, request.getParameter("newIdImportance"));
		}
		String withTask=request.getParameter("withTask");
		if (withTask!=null){
			if (needAnd){
				secondQueryPart.append(" and ");
			}
			if (!wasWhere){
				secondQueryPart.append(WHERE);
				wasWhere=true;
			}
			queryStr.append(", Task t ");
			secondQueryPart.append("o.task=t and t.id="+request.getParameter("idTask"));
			ISetOperations operation = new ISetOperations() {
				
				@Override
				public void doSet(ITableClass tableObject, String param) {
					Document doc = (Document) tableObject;
					if (changeIfNull(param)!=null){
						Task task= new Task();
						task.setParamForFindQuery(Integer.valueOf(param));
						task = (Task) DBWork.getInstance().findObjectFromTable(task);
						if  (doc.getTask()!=null){
							doc.getTask().removeDocument(doc);
						}
						task.addDocument(doc);
					} else {
						doc.setTask(null);
					}
				}
			};
			opData.addNewOperation(operation, request.getParameter("newIdTask"));
		}
		if (!wasWhere){
			return null;
		}
		queryStr.append(secondQueryPart);
		Collection<ITableClass> documents = (Collection<ITableClass>) DBWork.getInstance().getTableObjects(queryStr.toString());
		for (ITableClass i : documents) {
			opData.doOperations(i);
		}
		return documents;
	}
	
	public Collection<ITableClass> getTaskTypes(HttpServletRequest request){
		try {
			request.setCharacterEncoding("UTF-8");
		} catch (UnsupportedEncodingException e) {
			MyLogger.getInstance().getLog().error(MyLogger.CHARSET_EXCEPTION, e);
		}
		String queryStr=new String("SELECT o FROM TaskType o WHERE o.id="+request.getParameter("taskTypeName"));
		OperationsData opData=new OperationsData();
		ISetOperations operation = new ISetOperations() {
			
			@Override
			public void doSet(ITableClass tableObject, String param) {
				TaskType taskType = (TaskType) tableObject;
				taskType.setTypeName(param);
			}
		};
		opData.addNewOperation(operation, request.getParameter("newTaskTypeName"));
		Collection<ITableClass> taskTypes = (Collection<ITableClass>) DBWork.getInstance().getTableObjects(queryStr);
		for (ITableClass i : taskTypes) {
			opData.doOperations(i);
		}
		return taskTypes;
	}

	public Collection<ITableClass> getImportances(HttpServletRequest request){
		try {
			request.setCharacterEncoding("UTF-8");
		} catch (UnsupportedEncodingException e) {
			MyLogger.getInstance().getLog().error(MyLogger.CHARSET_EXCEPTION, e);
		}
		String queryStr=new String("SELECT o FROM Importance o WHERE o.id="+request.getParameter("importanceName"));
		OperationsData opData=new OperationsData();
		ISetOperations operation = new ISetOperations() {
			
			@Override
			public void doSet(ITableClass tableObject, String param) {
				Importance importance = (Importance) tableObject;
				importance.setType(param);
			}
		};
		opData.addNewOperation(operation, request.getParameter("newImportanceName"));
		Collection<ITableClass> importances = (Collection<ITableClass>) DBWork.getInstance().getTableObjects(queryStr);
		for (ITableClass i : importances) {
			opData.doOperations(i);
		}
		return importances;
	}
	
	public Collection<ITableClass> getAgentsAndTasks(HttpServletRequest request){
		try {
			request.setCharacterEncoding("UTF-8");
		} catch (UnsupportedEncodingException e) {
			MyLogger.getInstance().getLog().error(MyLogger.CHARSET_EXCEPTION, e);
		}
		StringBuffer queryStr=new StringBuffer("SELECT o FROM Agentsandtask o");
		StringBuffer secondQueryPart=new StringBuffer();
		boolean needAnd=false;
		boolean wasWhere=false;
		OperationsData opData=new OperationsData();
		String withAgent=request.getParameter("withAgent");
		if (withAgent!=null){
			secondQueryPart.append(WHERE);
			wasWhere=true;
			queryStr.append(", Agent a ");
			secondQueryPart.append("o.agent=a and a.id="+request.getParameter("idAgent"));
			needAnd=true;
			ISetOperations operation = new ISetOperations() {
				
				@Override
				public void doSet(ITableClass tableObject, String param) {
					Agentsandtask agentAndTask = (Agentsandtask) tableObject;
					if (changeIfNull(param)!=null){
						Agent agent= new Agent();
						agent.setParamForFindQuery(Integer.valueOf(param));
						agent = (Agent) DBWork.getInstance().findObjectFromTable(agent);
						if (agentAndTask.getAgent()!=null){
							agentAndTask.getAgent().removeAgentsandtask(agentAndTask);
						}
						agent.addAgentsandtask(agentAndTask);
					} else {
						agentAndTask.setAgent(null);
					}
				}
			};
			opData.addNewOperation(operation, request.getParameter("newIdAgent"));
		}
		String withTask=request.getParameter("withTask");
		if (withTask!=null){
			if (needAnd){
				secondQueryPart.append(" and ");
			}
			if (!wasWhere){
				secondQueryPart.append(WHERE);
				wasWhere=true;
			}
			queryStr.append(", Task t ");
			secondQueryPart.append("o.task=t and t.id="+request.getParameter("idTask"));
			ISetOperations operation = new ISetOperations() {
				
				@Override
				public void doSet(ITableClass tableObject, String param) {
					Agentsandtask agentAndTask = (Agentsandtask) tableObject;
					if (changeIfNull(param)!=null){
						Task task= new Task();
						task.setParamForFindQuery(Integer.valueOf(param));
						task = (Task) DBWork.getInstance().findObjectFromTable(task);
						if (agentAndTask.getTask()!=null){
							agentAndTask.getTask().removeAgentsandtask(agentAndTask);
						}
						task.addAgentsandtask(agentAndTask);
					} else {
						agentAndTask.setTask(null);
					}
				}
			};
			opData.addNewOperation(operation, request.getParameter("newIdTask"));
		}
		if (!wasWhere){
			return null;
		}
		queryStr.append(secondQueryPart);
		Collection<ITableClass> agentsandtasks = (Collection<ITableClass>) DBWork.getInstance().getTableObjects(queryStr.toString());
		for (ITableClass i : agentsandtasks) {
			opData.doOperations(i);
		}
		return agentsandtasks;
	}
	
}
